package com.excelcreatetable.demo.saveData;/**
 * @author wzj
 * @date 2020/3/17 14:27
 * @version 1.0
 */

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.excelcreatetable.demo.dbconnect.DBConnect;
import com.excelcreatetable.demo.threadLocalShare.ThreadLocalShare;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * auther wzj
 * date 2020/3/17 14:27
 * 将读取到的excel保存数据库
 */
@Component
public class SaveData {

    //数据库表名
    public static String dataTableName;
    //数据库列名
    public static String[] dataCloumn;


    /**
     *
     * @param tableName 文件路径
     * @param cloumn 列名
     */
    public void saveData(String filePath,String tableName,String[] cloumn){
        dataTableName=tableName;
        dataCloumn=cloumn;
        EasyExcel.read(filePath,new SaveDataListener()).sheet().doRead();

    }


}

//监听器
@Slf4j
class SaveDataListener extends AnalysisEventListener<Map<Integer, String>> {

    private final static Logger logger = LoggerFactory.getLogger(SaveDataListener.class);

    List<Map<Integer,String>> list = new ArrayList<>();

    //每次多少数据保存一次
    private Integer size = 35000;

    @Override
    public void invoke(Map<Integer, String> integerStringMap, AnalysisContext analysisContext) {
        logger.info("读取到的数据==="+ JSON.toJSONString(integerStringMap));

        list.add(integerStringMap);

        if (list.size()>=size){
            saveDate(list);
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

        saveDate(list);
        logger.info("所有数据解析完成");

    }
    //excel保存
    public void saveDate(List<Map<Integer,String>> list){
        Connection connection = DBConnect.getConnection();
        try {
            //设置不自动提交
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String[] clounm = SaveData.dataCloumn;
        //
        String cloumnName="";
        String value = "";
        for (int i = 0; i < clounm.length; i++) {
           cloumnName += clounm[i];
           value+="?";
           if (i<clounm.length-1){
               cloumnName+=",";
               value+=",";
           }
        }

        String sql = "insert into "+SaveData.dataTableName +"("+cloumnName+")"+" values("+value+")";
        System.out.println("保存数据的sql = " + sql);
        log.info("exlce保存"+SaveData.dataTableName+"表的sql为:"+sql);
        try {
            PreparedStatement ps = connection.prepareStatement(sql);

            String[] heads = ThreadLocalShare.head.get();
            //读取到的数据
            for (int i = 0; i < list.size(); i++) {
                Map<Integer, String> integerStringMap = list.get(i);
                //必须为heads.length 如果excel后几个几个单元格为空会报异常
                for (int i2=0;i2<heads.length;i2++){
                    String s = integerStringMap.get(i2);
                    ps.setString(i2+1,s);
                }
                ps.addBatch();
                if (i%1000==0){
                    ps.executeBatch();
                    connection.commit();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            connection.commit();
            ps.clearBatch();
            log.info("excel插入"+SaveData.dataTableName+"表数据成功");
        } catch (SQLException e) {
            log.error("excel插入"+SaveData.dataTableName+"表失败"+"报错的sql为:"+sql);
            e.printStackTrace();
        }
    }
}
